package com.reform.service.impl;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.reform.config.SchoolDB;
import com.reform.pojo.admin.Admin;
import com.reform.pojo.common.Department;
import com.reform.pojo.teacher.Teacher;
import com.reform.pojo.teacher.TeacherInfo;
import com.reform.service.IAdminService;
import com.reform.service.IDepartmentService;
import com.reform.service.ITeacherService;
import com.reform.service.SchoolDBService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.sql.*;
import java.util.*;

@Service
public class SchoolDBServiceImpl implements SchoolDBService {

    private Connection connection;
    private Statement statement;
    private ResultSet rs;

    @Autowired
    ITeacherService teacherService;

    @Autowired
    IAdminService adminService;

    @Autowired
    IDepartmentService departmentService;


    /**
     * 获取基本资源
     *
     * @param sql
     * @throws SQLException
     */
    public void getBase(String sql) throws SQLException {
        connection = SchoolDB.getConnect();
        statement = connection.createStatement();
        rs = statement.executeQuery(sql);
    }

    /**
     * 关闭基本资源
     *
     * @throws SQLException
     */
    public void closeBase() throws SQLException {
        connection.close();
        statement.close();
        rs.close();
    }

    /**
     * 获取全校教师信息
     *
     * @return
     * @throws SQLException
     */
    @Override
    public List<TeacherInfo> selectAll() throws SQLException {

        List<Admin> adminList = adminService.list(new QueryWrapper<Admin>().eq("type", 1));
        List<Admin> operateAdminList = adminService.list(new QueryWrapper<Admin>().eq("type", 0));

        String depIdList = "";
        String typeList = "";

        if (adminList.size() > 0) {
            depIdList += "'" + adminList.get(0).getGh() + "'";
            for (int i = 1; i < adminList.size(); i++) {
                depIdList += ",'" + adminList.get(i).getGh() + "'";
            }
        }
        if (operateAdminList.size() > 0) {
            typeList += "'" + operateAdminList.get(0).getGh() + "'";
            for (int i = 1; i < operateAdminList.size(); i++) {
                typeList += ",'" + operateAdminList.get(i).getGh() + "'";
            }
        }

        String sql = "select xm,gh,sfzh,bm,bmbh,lxdh,jtgw,xpprzc,1 AS isAdmin,1 AS isOperateAdmin from teacher_info WHERE gh in (" + depIdList + ")" +
                " union ";
        if (!"".equals(typeList)) {
            sql += "select xm,gh,sfzh,bm,bmbh,lxdh,jtgw,xpprzc,1 AS isAdmin,0 AS isOperateAdmin from teacher_info WHERE gh in (" + typeList + ")" +
                    " union " +
                    " select xm,gh,sfzh,bm,bmbh,lxdh,jtgw,xpprzc,null AS isAdmin,null AS isOperateAdmin from teacher_info WHERE gh not in (" + typeList + ")" + " union ";
        }
        sql += " select xm,gh,sfzh,bm,bmbh,lxdh,jtgw,xpprzc,null AS isAdmin,null AS isOperateAdmin from teacher_info WHERE gh not in (" + depIdList + ")";
        getBase(sql);
        List<TeacherInfo> teacherInfoList = new ArrayList<>();
        while (rs.next()) {
            String gh = rs.getString("gh");
            String xm = rs.getString("xm");
            String sfzh = rs.getString("sfzh");
            String bm = rs.getString("bm");
            String bmbh = rs.getString("bmbh");
            String lxdh = rs.getString("lxdh");
            String jtgw = rs.getString("jtgw");
            String xpprzc = rs.getString("xpprzc");
            boolean isAdmin = rs.getBoolean("isAdmin");
            boolean isOperateAdmin = rs.getBoolean("isOperateAdmin");
            TeacherInfo teacherInfo = new TeacherInfo(xm, gh, sfzh, bm, bmbh, lxdh, jtgw, xpprzc, isAdmin, isOperateAdmin);
            teacherInfoList.add(teacherInfo);
        }
        closeBase();
        return teacherInfoList;
    }

    /**
     * 根据教师工号获取教师信息
     *
     * @param gh
     * @return
     * @throws SQLException
     */
    @Override
    public Map<String, String> getTeacherInfoByGh(String gh) throws SQLException {
        String sql = "select xm,gh,sfzh,bm,bmbh,lxdh,jtgw,xpprzc from teacher_info where gh=" + gh;
        getBase(sql);
        Map<String, String> teacherMap = null;
        while (rs.next()) {
            teacherMap = new HashMap<>();
            String xm = rs.getString("xm");
            String sfzh = rs.getString("sfzh");
            String bm = rs.getString("bm");
            String bmbh = rs.getString("bmbh");
            String lxdh = rs.getString("lxdh");
            String jtgw = rs.getString("jtgw");
            String xpprzc = rs.getString("xpprzc");
            Teacher teacher = teacherService.getOne(new QueryWrapper<Teacher>().eq("gh", gh));
            String password = "*****";
            String email = null;
            if (teacher != null) {
                email = teacher.getEmail();
            }
            teacherMap.put("gh", gh);
            teacherMap.put("xm", xm);
            teacherMap.put("sfzh", sfzh);
            teacherMap.put("bm", bm);
            teacherMap.put("bmbh", bmbh);
            teacherMap.put("lxdh", lxdh);
            teacherMap.put("jtgw", jtgw);
            teacherMap.put("xpprzc", xpprzc);
            teacherMap.put("password", password);
            teacherMap.put("email", email);
        }
        closeBase();
        return teacherMap;
    }

    /**
     * 获取全校部门信息
     *
     * @return
     */
    @Override
    public List<Department> getAllDepartments() throws SQLException {
        List<Admin> adminList = adminService.list(new QueryWrapper<Admin>().ne("dep_id", 0).eq("type", 1));
        List<Department> departmentList = departmentService.list();
        String depIdList = "";
        String ghList = "";
        String au_depIdList = "";
        if (adminList.size() > 0) {
            depIdList += "'" + adminList.get(0).getDepId() + "'";
            ghList += "'" + adminList.get(0).getGh() + "'";
            for (int i = 1; i < adminList.size(); i++) {
                depIdList += ",'" + adminList.get(i).getDepId() + "'";
                ghList += ",'" + adminList.get(i).getGh() + "'";
            }
        }
        if (departmentList.size() > 0) {
            au_depIdList += "'" + departmentList.get(0).getDepId() + "'";
            for (int i = 1; i < departmentList.size(); i++) {
                au_depIdList += ",'" + departmentList.get(i).getDepId() + "'";
            }
        }
//        String sql = "SELECT DISTINCT bm,bmbh FROM teacher_info where bmbh IS NOT NULL AND bmbh != '' ORDER BY bmbh;";
//        String sql = "SELECT  bm,bmbh,xm FROM teacher_info where  gh in (" +ghList+ ") union " +
//                "SELECT  bm,bmbh,null as xm FROM teacher_info where bmbh IS NOT NULL AND bmbh != ''and bmbh not in ("+depIdList+ ") GROUP BY bmbh;";
//
        String sql = "select bm,bmbh,xm,1 as sign from (SELECT  bm,bmbh,xm FROM teacher_info where  gh in (" + ghList + ") union " +
                "SELECT  bm,bmbh,NULL as xm FROM teacher_info where bmbh IS NOT NULL AND bmbh != '' and bmbh not in (" + depIdList + ") GROUP BY bmbh) as temp where bmbh in(" + au_depIdList + ") union " +
                "SELECT bm,bmbh,xm,0 as sign from (SELECT  bm,bmbh,xm FROM teacher_info where  gh in (" + ghList + ") union " +
                "SELECT  bm,bmbh,NULL as xm FROM teacher_info where bmbh IS NOT NULL AND bmbh != '' and bmbh not in (" + depIdList + ") GROUP BY bmbh) as temp where bmbh not in(" + au_depIdList + ")";
        getBase(sql);
        departmentList = new ArrayList<Department>();
        while (rs.next()) {
            int dep_id = rs.getInt("bmbh");
            String dep_name = rs.getString("bm");
            String teacher_name = rs.getString("xm");
            boolean sign = rs.getBoolean("sign");
            Department department = new Department();
            department.setDepId(dep_id);
            department.setDepName(dep_name);
            department.setDepAdmin(teacher_name);
            department.setDepAuth(sign);
//            //判断是否已有操作管理员
//            Admin admin = adminService.getOne(new QueryWrapper<Admin>().eq("dep_id", dep_id).eq("type", 1));
//            if (admin != null) {
//                //根据gh获取教师名
//                String adminName = teacherService.getTeacherNameByGh(admin.getGh());
//                department.setDepAdmin(xm);
//            } else {
//                department.setDepAdmin("");
//            }
//
//            //判断部门是否已授权
//            Department departmentServiceOne = departmentService.getOne(new QueryWrapper<Department>().eq("dep_id", dep_id));
//            department.setDepAuth(departmentServiceOne != null);
            departmentList.add(department);
        }
        Collections.sort(departmentList);
        return departmentList;
    }

    @Override
    public List<Department> getDepartmentsByArgs(String args) throws SQLException {
        String sql = "SELECT DISTINCT bm,bmbh FROM teacher_info where bm like '%" + args + "%' and bmbh IS NOT NULL AND bmbh != '' ORDER BY bmbh;";
        getBase(sql);
        List<Department> departmentList = new ArrayList<Department>();
        while (rs.next()) {
            int dep_id = rs.getInt("bmbh");
            String dep_name = rs.getString("bm");
            Department department = new Department();
            department.setDepId(dep_id);
            department.setDepName(dep_name);
            departmentList.add(department);
        }
        Collections.sort(departmentList);
        return departmentList;
    }

    @Override
    public String getTeacherXmByGh(String gh) throws SQLException {
        String sql = "select xm from teacher_info where gh = " + gh;
        getBase(sql);
        String name = "";
        while (rs.next()) {
            String xm = rs.getString("xm");
            name = xm;
        }
        return name;
    }

    @Override
    public String getTeacherDepByGh(String gh) throws SQLException {
        String sql = "select bm from teacher_info where gh = " + gh;
        getBase(sql);
        String bm = "";
        while (rs.next()) {
            bm = rs.getString("bm");
        }
        return bm;
    }

    @Override
    public List<Department> getSimplifyDepartment() throws SQLException {
        String sql = "SELECT DISTINCT bm,bmbh FROM teacher_info where bmbh IS NOT NULL AND bmbh != '' ORDER BY bmbh;";
        getBase(sql);
        List<Department> departmentList = new ArrayList<Department>();
        while (rs.next()) {
            int dep_id = rs.getInt("bmbh");
            String dep_name = rs.getString("bm");
            Department department = new Department();
            department.setDepId(dep_id);
            department.setDepName(dep_name);
            departmentList.add(department);
        }
        Collections.sort(departmentList);
        return departmentList;
    }

    @Override
    public List<String> getAllGH() throws SQLException {
        String sql = "SELECT gh FROM `teacher_info`";
        getBase(sql);
        List<String> ghList = new ArrayList<String>();
        while (rs.next()) {
            String gh = rs.getString("gh");
            ghList.add(gh);
        }
        return ghList;
    }


}
